package com.travel.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.RowSet;

import com.travel.dao.HotelDao;
import com.travel.entity.Hotel;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

/**
* @Title HotelDaoImpl.java
* @Package com.travel.dao.impl
* @Author sixmarkblue
* @Time 2020年10月27日 上午10:11:08
* @Version 1.0
* @Description: TODO
*/
public class HotelDaoImpl implements HotelDao {

	private DBUtils dbUtil = new DBUtils();
	/**
	 * 插入hotel表的方法
	 */
	@Override
	public int insertTabHotel(Hotel hotel) {

		String sql = "insert into tab_hotel values(null,?,?,?,?,?,?)";
		int i = dbUtil.update(sql, hotel.getHotelName(),hotel.getHotelDesc(),hotel.getHotelPicture(),hotel.getHotelPhone(),hotel.getHotelAddress(),hotel.getHotelStar());
		
		return i;
	}
	
	/**
	 * 删除酒店信息的方法
	 */
	@Override
	public int deleteFromTabHotel(int hotelId) {
		
		String sql = "delete from tab_hotel where hotel_id = ?";
		int i = dbUtil.update(sql, hotelId);
		return i;
	}
	
	/**
	 * 根据hotelId修改酒店信息的方法
	 */
	@Override
	public int updateTabHotel(int hotelId, Hotel hotel) {

		String sql = "update tab_hotel set hotel_name=?,hotel_desc=?,hotel_picture=?,hotel_phone=?,hotel_address=?,hotel_star=? where hotel_id=?";
		int i = dbUtil.update(sql, hotel.getHotelName(),hotel.getHotelDesc(),hotel.getHotelPicture(),hotel.getHotelPhone()
				,hotel.getHotelAddress(),hotel.getHotelStar(),hotelId);
		return i;
	}
	
	
	@Override
	public List<Hotel> queryAllHotels(){

		String sql = "select hotel_id,hotel_name,hotel_desc,hotel_picture,hotel_phone,hotel_address,hotel_star from tab_hotel";
				
		RowSet rs = dbUtil.query(sql);
		List<Hotel> list = new ArrayList<Hotel>();
		try {
			while (rs.next()) {
				int hotelId= rs.getInt("hotel_id");
				String hotelName = rs.getString("hotel_name");
				String hotelDesc = rs.getString("hotel_desc");
				String hotelPicture = rs.getString("hotel_picture");
				String hotelPhone = rs.getString("hotel_phone");
				String hotelAddress = rs.getString("hotel_address");
				String hotelStar = rs.getString("hotel_star");
				
				Hotel hotel = new Hotel(hotelId, hotelName, hotelDesc, hotelPicture, hotelPhone, hotelAddress, hotelStar);
				list.add(hotel);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据当前页码、每页显示个数、酒店名查询酒店记录
	 * @param pageNum 当前页码
	 * @param pageSize 每页显示个数
	 * @param hotelName 酒店名
	 * @return 分页工具类
	 */
	@Override
	public PageInfo<Hotel> queryByPageAndName(int pageNum, int pageSize, String hotelName) {
		
		// 1.获取分页结果的数据
		List<Hotel> data = new ArrayList<Hotel>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		  .append(" hotel_id,")
		  .append(" hotel_name,")
		  .append(" hotel_desc,")
		  .append(" hotel_picture,")
		  .append(" hotel_phone,")
		  .append(" hotel_address,")
		  .append(" hotel_star")
		  .append(" from tab_hotel ")
		  .append(" where hotel_name like concat('%',?,'%')")
		  .append(" limit ?,?");
		
		ResultSet rs  = dbUtil.query(sb.toString(), hotelName, (pageNum-1)*pageSize, pageSize);
		
		// 遍历结果集
		try {
			while (rs.next()) {
				int hotelId= rs.getInt("hotel_id");
				String hotelName2 = rs.getString("hotel_name");
				String hotelDesc = rs.getString("hotel_desc");
				String hotelPicture = rs.getString("hotel_picture");
				String hotelPhone = rs.getString("hotel_phone");
				String hotelAddress = rs.getString("hotel_address");
				String hotelStar = rs.getString("hotel_star");
				
				// 构建实体模型
				Hotel hotel = new Hotel(hotelId, hotelName2, hotelDesc, hotelPicture, hotelPhone, hotelAddress, hotelStar);
				// 添加到集合中
				data.add(hotel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 2.获取总记录数(未分页之前)
		int total = 0;
		sb = new StringBuffer();
		sb.append("select")
		  .append(" count(*)")
		  .append(" from tab_hotel ")
		  .append(" where hotel_name like concat('%',?,'%')");
		
		rs  = dbUtil.query(sb.toString(), hotelName);
		// 遍历结果集
		try {
			while (rs.next()) {
				total  = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 构建分页对象
		PageInfo<Hotel> pageInfo = new PageInfo<Hotel>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public List<Hotel> queryHotelById(int hotelId) {
		
		List<Hotel> list = new ArrayList<Hotel>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		  .append(" hotel_id,")
		  .append(" hotel_name,")
		  .append(" hotel_desc,")
		  .append(" hotel_picture,")
		  .append(" hotel_phone,")
		  .append(" hotel_address,")
		  .append(" hotel_star")
		  .append(" from tab_hotel ")
		  .append(" where hotel_id = ?");
		
		ResultSet rs  = dbUtil.query(sb.toString(),hotelId);
		
		// 遍历结果集
		try {
			while (rs.next()) {
				int hotelId2= rs.getInt("hotel_id");
				String hotelName = rs.getString("hotel_name");
				String hotelDesc = rs.getString("hotel_desc");
				String hotelPicture = rs.getString("hotel_picture");
				String hotelPhone = rs.getString("hotel_phone");
				String hotelAddress = rs.getString("hotel_address");
				String hotelStar = rs.getString("hotel_star");
				
				// 构建实体模型
				Hotel hotel = new Hotel(hotelId2, hotelName, hotelDesc, hotelPicture, hotelPhone, hotelAddress, hotelStar);
				// 添加到集合中
				list.add(hotel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 模态框修改Hotel的方法  此方法没有对hotel_id与hotel_picture进行修改
	 */
	@Override
	public int modifyHotel(Hotel hotel) {
		
		String sql = "update tab_hotel set hotel_name=?,hotel_desc=?,hotel_phone=?,hotel_address=?,hotel_star=? where hotel_id = ?";
		int i = dbUtil.update(sql,hotel.getHotelName(),hotel.getHotelDesc(),hotel.getHotelPhone(),hotel.getHotelAddress(),hotel.getHotelStar(),hotel.getHotelId());
		return i;
	}

	/**
	 * 模态框修改Hotel的方法，此方法对hotel_picture进行了修改
	 * @param hotel 带有hotelPicture的Hotel
	 * @return
	 */
	@Override
	public int modifyHotelWithHotelPicture(Hotel hotel) {

		String sql = "update tab_hotel set hotel_picture=?,hotel_name=?,hotel_desc=?,hotel_phone=?,hotel_address=?,hotel_star=? where hotel_id = ?";
		int i = dbUtil.update(sql,hotel.getHotelPicture(),hotel.getHotelName(),hotel.getHotelDesc(),hotel.getHotelPhone(),hotel.getHotelAddress(),hotel.getHotelStar(),hotel.getHotelId());
		return i;
	}
	
	@Override
	public List<Hotel> recommendHotels() {
		List<Hotel> list = new ArrayList<Hotel>();
		StringBuffer sb = new StringBuffer();
		sb.append(
				"SELECT H.hotel_name,H.hotel_address,H.hotel_picture,H.hotel_id \r\n" + 
				"from tab_room R \r\n" + 
				"JOIN tab_hotel_order O ON R.room_id=O.room_id \r\n" + 
				"JOIN tab_hotel H  ON H.hotel_id=R.hotel_id \r\n" + 
				"GROUP BY O.room_id ORDER BY count(O.room_id) desc LIMIT 6");

		ResultSet rs = dbUtil.query(sb.toString());

		// 遍历结果集
		try {
			while (rs.next()) {
					int hotelId = rs.getInt("hotel_id");
					String hotelName = rs.getString("hotel_name");
					String hotelPicture = rs.getString("hotel_picture");
					String hotelAddress = rs.getString("hotel_address");
					
					// 构建实体模型
					Hotel hotel = new Hotel(hotelId, hotelName, null, hotelPicture, null, hotelAddress, null);
					// 添加到集合中
					list.add(hotel);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}














